// 数据库查询封装
import { getDB } from './index.js'

// ============ 分类查询 ============

export function getCategories(status = 1) {
  const db = getDB()
  const stmt = db.prepare(`
    SELECT 
      c.*,
      COUNT(i.id) as count
    FROM Category c
    LEFT JOIN Image i ON c.id = i.categoryId AND i.status = 1
    WHERE c.status = ?
    GROUP BY c.id
    ORDER BY c.sort ASC, c.id ASC
  `)
  return stmt.all(status)
}

export function getCategoriesWithImageCount(options = {}) {
  const { status = 1 } = options
  return getCategories(status)
}

export function getCategoryById(id) {
  const db = getDB()
  const stmt = db.prepare('SELECT * FROM Category WHERE id = ?')
  return stmt.get(id)
}

export function getCategoryBySlug(slug) {
  const db = getDB()
  const stmt = db.prepare('SELECT * FROM Category WHERE slug = ?')
  return stmt.get(slug)
}

export function createCategory(data) {
  const db = getDB()
  const stmt = db.prepare(`
    INSERT INTO Category (name, slug, description, sort, status)
    VALUES (?, ?, ?, ?, ?)
  `)
  const result = stmt.run(
    data.name,
    data.slug,
    data.description || null,
    data.sort || 0,
    data.status !== undefined ? data.status : 1
  )
  return getCategoryById(result.lastInsertRowid)
}

export function updateCategory(id, data) {
  const db = getDB()
  const fields = []
  const values = []
  
  if (data.name !== undefined) {
    fields.push('name = ?')
    values.push(data.name)
  }
  if (data.slug !== undefined) {
    fields.push('slug = ?')
    values.push(data.slug)
  }
  if (data.description !== undefined) {
    fields.push('description = ?')
    values.push(data.description)
  }
  if (data.sort !== undefined) {
    fields.push('sort = ?')
    values.push(data.sort)
  }
  if (data.status !== undefined) {
    fields.push('status = ?')
    values.push(data.status)
  }
  
  if (fields.length === 0) {
    return getCategoryById(id)
  }
  
  values.push(id)
  const stmt = db.prepare(`UPDATE Category SET ${fields.join(', ')} WHERE id = ?`)
  stmt.run(...values)
  return getCategoryById(id)
}

export function deleteCategory(id) {
  const db = getDB()
  // 检查是否有图片关联
  const imageCount = db.prepare('SELECT COUNT(*) as count FROM Image WHERE categoryId = ?').get(id)
  if (imageCount.count > 0) {
    throw new Error(`该分类下还有 ${imageCount.count} 张图片，无法删除`)
  }
  const stmt = db.prepare('DELETE FROM Category WHERE id = ?')
  stmt.run(id)
  return { message: '删除成功' }
}

// ============ 图片查询 ============

export function getImages(options = {}) {
  const db = getDB()
  const {
    page = 1,
    pageSize = 20,
    offset,
    limit,
    category,
    categoryId,
    categorySlug,
    status = 1,
    keyword
  } = options
  
  let whereClause = 'WHERE i.status = ?'
  const params = [status]
  
  // 分类筛选
  if (categoryId) {
    whereClause += ' AND i.categoryId = ?'
    params.push(categoryId)
  } else if (categorySlug) {
    const cat = getCategoryBySlug(categorySlug)
    if (cat) {
      whereClause += ' AND i.categoryId = ?'
      params.push(cat.id)
    }
  } else if (category) {
    // 支持 slug 或 id
    if (isNaN(category)) {
      const cat = getCategoryBySlug(category)
      if (cat) {
        whereClause += ' AND i.categoryId = ?'
        params.push(cat.id)
      }
    } else {
      whereClause += ' AND i.categoryId = ?'
      params.push(parseInt(category))
    }
  }
  
  if (keyword) {
    whereClause += ' AND i.title LIKE ?'
    params.push(`%${keyword}%`)
  }
  
  const skip = offset !== undefined ? offset : (page - 1) * pageSize
  const pageSizeValue = limit !== undefined ? limit : pageSize
  
  // 查询列表
  const listStmt = db.prepare(`
    SELECT 
      i.*,
      c.id as category_id,
      c.name as category_name,
      c.slug as category_slug
    FROM Image i
    LEFT JOIN Category c ON i.categoryId = c.id
    ${whereClause}
    ORDER BY i.createdAt DESC
    LIMIT ? OFFSET ?
  `)
  
  const list = listStmt.all(...params, pageSize, skip).map(row => ({
    id: row.id,
    title: row.title,
    url: row.url,
    thumbnail: row.thumbnail,
    width: row.width,
    height: row.height,
    size: row.size,
    categoryId: row.categoryId,
    status: row.status,
    views: row.views,
    likes: row.likes,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
    category: row.category_id ? {
      id: row.category_id,
      name: row.category_name,
      slug: row.category_slug
    } : null
  }))
  
  // 查询总数
  const countStmt = db.prepare(`SELECT COUNT(*) as total FROM Image i ${whereClause}`)
  const total = countStmt.get(...params).total
  
  return {
    list,
    total,
    page,
    pageSize
  }
}

export function searchImages(options = {}) {
  return getImages(options)
}

export function getImageById(id) {
  const db = getDB()
  const stmt = db.prepare(`
    SELECT 
      i.*,
      c.id as category_id,
      c.name as category_name,
      c.slug as category_slug
    FROM Image i
    LEFT JOIN Category c ON i.categoryId = c.id
    WHERE i.id = ?
  `)
  const row = stmt.get(id)
  if (!row) return null
  
  return {
    id: row.id,
    title: row.title,
    url: row.url,
    thumbnail: row.thumbnail,
    width: row.width,
    height: row.height,
    size: row.size,
    categoryId: row.categoryId,
    status: row.status,
    views: row.views,
    likes: row.likes,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
    category: row.category_id ? {
      id: row.category_id,
      name: row.category_name,
      slug: row.category_slug
    } : null
  }
}

export function createImage(data) {
  const db = getDB()
  const stmt = db.prepare(`
    INSERT INTO Image (title, url, thumbnail, categoryId, width, height, size, status)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  `)
  const result = stmt.run(
    data.title,
    data.url,
    data.thumbnail || null,
    data.categoryId,
    data.width || null,
    data.height || null,
    data.size || null,
    data.status !== undefined ? data.status : 1
  )
  return getImageById(result.lastInsertRowid)
}

export function updateImage(id, data) {
  const db = getDB()
  const fields = []
  const values = []
  
  if (data.title !== undefined) {
    fields.push('title = ?')
    values.push(data.title)
  }
  if (data.url !== undefined) {
    fields.push('url = ?')
    values.push(data.url)
  }
  if (data.thumbnail !== undefined) {
    fields.push('thumbnail = ?')
    values.push(data.thumbnail)
  }
  if (data.categoryId !== undefined) {
    fields.push('categoryId = ?')
    values.push(data.categoryId)
  }
  if (data.width !== undefined) {
    fields.push('width = ?')
    values.push(data.width)
  }
  if (data.height !== undefined) {
    fields.push('height = ?')
    values.push(data.height)
  }
  if (data.size !== undefined) {
    fields.push('size = ?')
    values.push(data.size)
  }
  if (data.status !== undefined) {
    fields.push('status = ?')
    values.push(data.status)
  }
  
  if (fields.length === 0) {
    return getImageById(id)
  }
  
  values.push(id)
  const stmt = db.prepare(`UPDATE Image SET ${fields.join(', ')} WHERE id = ?`)
  stmt.run(...values)
  return getImageById(id)
}

export function deleteImage(id) {
  const db = getDB()
  const stmt = db.prepare('DELETE FROM Image WHERE id = ?')
  stmt.run(id)
  return { message: '删除成功' }
}

export function incrementImageViews(id) {
  const db = getDB()
  const stmt = db.prepare('UPDATE Image SET views = views + 1 WHERE id = ?')
  stmt.run(id)
}

export function batchCreateImages(images) {
  const database = getDB()
  const insertStmt = database.prepare(`
    INSERT INTO Image (title, url, thumbnail, categoryId, width, height, size, status)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  `)
  
  let success = 0
  let failed = 0
  const ids = []
  
  for (const img of images) {
    try {
      const { title, url, thumbnail, categoryId, width, height, size } = img
      
      if (!title || !url || !categoryId) {
        failed++
        continue
      }
      
      const result = insertStmt.run(
        title,
        url,
        thumbnail || null,
        parseInt(categoryId),
        width ? parseInt(width) : null,
        height ? parseInt(height) : null,
        size ? parseInt(size) : null,
        1 // 默认上架
      )
      ids.push(result.lastInsertRowid)
      success++
    } catch (error) {
      failed++
    }
  }
  
  return { success, failed, ids }
}

// ============ 分类管理查询（Admin） ============

export function getCategoriesForAdmin(options = {}) {
  const db = getDB()
  const { page = 1, pageSize = 20, status } = options
  
  let whereClause = ''
  const params = []
  
  if (status !== undefined) {
    whereClause = 'WHERE status = ?'
    params.push(status)
  }
  
  const skip = (page - 1) * pageSize
  
  const listStmt = db.prepare(`
    SELECT * FROM Category
    ${whereClause}
    ORDER BY sort ASC, id ASC
    LIMIT ? OFFSET ?
  `)
  
  const list = listStmt.all(...params, pageSize, skip)
  
  const countStmt = db.prepare(`SELECT COUNT(*) as total FROM Category ${whereClause}`)
  const total = countStmt.get(...params).total
  
  return { list, total, page, pageSize }
}

// ============ 图片管理查询（Admin） ============

export function getImagesForAdmin(options = {}) {
  const db = getDB()
  const {
    page = 1,
    pageSize = 20,
    categoryId,
    status,
    keyword
  } = options
  
  let whereClause = 'WHERE 1=1'
  const params = []
  
  if (categoryId) {
    whereClause += ' AND i.categoryId = ?'
    params.push(categoryId)
  }
  
  if (status !== undefined) {
    whereClause += ' AND i.status = ?'
    params.push(status)
  }
  
  if (keyword) {
    whereClause += ' AND i.title LIKE ?'
    params.push(`%${keyword}%`)
  }
  
  const skip = (page - 1) * pageSize
  
  const listStmt = db.prepare(`
    SELECT 
      i.*,
      c.id as category_id,
      c.name as category_name,
      c.slug as category_slug
    FROM Image i
    LEFT JOIN Category c ON i.categoryId = c.id
    ${whereClause}
    ORDER BY i.createdAt DESC
    LIMIT ? OFFSET ?
  `)
  
  const list = listStmt.all(...params, pageSize, skip).map(row => ({
    id: row.id,
    title: row.title,
    url: row.url,
    thumbnail: row.thumbnail,
    width: row.width,
    height: row.height,
    size: row.size,
    categoryId: row.categoryId,
    status: row.status,
    views: row.views,
    likes: row.likes,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
    category: row.category_id ? {
      id: row.category_id,
      name: row.category_name,
      slug: row.category_slug
    } : null
  }))
  
  const countStmt = db.prepare(`SELECT COUNT(*) as total FROM Image i ${whereClause}`)
  const total = countStmt.get(...params).total
  
  return { list, total, page, pageSize }
}

export function batchUpdateImageStatus(ids, status) {
  const db = getDB()
  const placeholders = ids.map(() => '?').join(',')
  const stmt = db.prepare(`UPDATE Image SET status = ? WHERE id IN (${placeholders})`)
  stmt.run(status, ...ids)
  return { message: '操作成功' }
}

